package cn.itcast.dao.impl;

import cn.itcast.dao.UserDao;
import cn.itcast.domain.User;
import cn.itcast.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author Wang Chengzhi
 * @create 2019-10-24 8:56
 */
public class UserDaoImpl implements UserDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());


    @Override
    public List<User> findAll() {

        String sql = "select * from s_user";
        //2.执行查询方法
        List<User> list = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return list;
    }

    @Override
    public void add(User user) {
        String sql = "insert into s_user values(null,?,?,?,?,?,?,null,null)";
        //2.执行插入方法
        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail());

    }

    @Override
    public void delete(int id) {
        String sql = "delete from s_user where id = ?";
        //2. 执行删除
        template.update(sql,id);
    }

    @Override
    public User select(int id) {
        String sql = "select * from s_user where id = ?";
        User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
        return user;
    }

    @Override
    public void update(User user) {
        String sql = "update s_user set name=?,gender=?,age=?,address=?,qq=?,email=? where id = ?";

        template.update(sql,user.getName(),user.getGender(),user.getAge(),user.getAddress(),user.getQq(),user.getEmail(),user.getId());

    }

    @Override
    public int getTotalCount(Map<String, String[]> condition) {

        String sql = "select count(*) from s_user where 1 = 1  ";

        StringBuilder sb = new StringBuilder(sql);

        Set<String> keySet = condition.keySet();

        //创建一个条件的集合
        List params = new ArrayList();

        for (String key : keySet) {
            //排除currentPage 和 rows
            if ("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            //获取value
            String[] values = condition.get(key);

            if (values[0] != null && !"".equals(values[0])){

                //拼接查询
                sb.append(" and "  + key + " like ? ");
                params.add("%" + values[0] + "%");
            }

        }
        sql = sb.toString();

        Integer i = template.queryForObject(sql, Integer.class,params.toArray());
        System.out.println(i);
        return i;
    }

    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {

        String sql = "select * from s_user where 1 = 1 ";

        StringBuilder sb = new StringBuilder(sql);

        Set<String> keySet = condition.keySet();

        List params = new ArrayList();

        for (String key : keySet) {

            if ("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }

            String value = condition.get(key)[0];
            System.out.println(value);
            if (value != null && !"".equals(value)){
                sb.append(" and " + key + " like ? ");
                params.add("%" + value + "%");
            }

        }
        sb.append(" limit ? , ? ");
        sql = sb.toString();
        System.out.println(sql);

        params.add(start);
        params.add(rows);

        List<User> list = template.query(sql, new BeanPropertyRowMapper<User>(User.class),params.toArray());

        return list;
    }

    @Override
    public User findUserByUsernameAndPassword(User user) {
        try{String sql = "select * FROM s_user where username = ? and password = ?";
        User loginUser = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), user.getUsername(), user.getPassword());}
        catch(DataAccessException e){e.printStackTrace();
            return null;}
        

        return loginUser;
    }


}
